package com.zt.dev.siteInfoRegister.util;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.beans.PropertyDescriptor;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @PackagingName:com.zt.dev.siteInfoRegister.util
 * @Author:sss
 * @Drscription:
 */
public class ExcelUtils {

    private Map<String,String> objMap = null;

    public ExcelUtils() {
    }

    /**
     *
     * @param objMap 传入指定的对象map
     */
    public ExcelUtils(Map<String,String> objMap){
        this.objMap = objMap;
    }

    /**------------------------------------------------导出--------------------------------------------------------**/

    public HSSFWorkbook getHSSFWorkbook(String sheetName, String[] title1, String[] title2, List values, String[] column) throws Exception {

        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet(sheetName);
        HSSFRow row = sheet.createRow(0);
        HSSFCellStyle style = wb.createCellStyle();

        //style.setAlignment(HorizontalAlignment.CENTER);
        //style.setVerticalAlignment(VerticalAlignment.CENTER);
        HSSFFont font = wb.createFont();
        font.setFontName("黑体");
        font.setFontHeightInPoints((short) 12);
        font.setBold(true);

        sheet.setColumnWidth(0,1500);
        int c = 0;
        for(String titles : title1){
            String[] str = titles.split(",");
            HSSFCell cell = row.createCell(c);
            if(1 != Integer.parseInt(str[2]) || 1 != Integer.parseInt(str[1])){
                sheet.addMergedRegion(new CellRangeAddress(0,Integer.parseInt(str[2])-1,c,c+Integer.parseInt(str[1])-1));
            }
            cell.setCellValue(str[0]);
            cell.setCellStyle(style);
            style.setFont(font);
            c += Integer.parseInt(str[1]);
        }

        row = sheet.createRow(1);
        char A = 65;
        for(int i=0; i<title2.length; i++){
            HSSFCell cell = row.createCell(i+1);
            cell.setCellValue(title2[i]);
            cell.setCellStyle(style);
            sheet.setColumnWidth(i+1,2*2500);
            A ++;
        }
        A = A > 90 ? 90 : A;
        if(A > 65){
            //筛选 A:65
            sheet.setAutoFilter(CellRangeAddress.valueOf("B2:" + A + "2"));
        }

        for(int i=0; i<values.size(); i++){
            List list = getConvertValue(values.get(i), column);
            row = sheet.createRow(i+2);
            row.createCell(0).setCellValue(i+1);
            for(int j=0; j<list.size(); j++){
                row.createCell(j+1).setCellValue(list.get(j).toString());
            }
        }
        return wb;
    }

    public List getConvertValue(Object obj,String[] column) throws Exception {
        List resList = new ArrayList();
        List<String> fieldList = new ArrayList();
        Class clazz = obj.getClass();
        Field[] fields = obj.getClass().getDeclaredFields();
        for(Field field : fields){
            fieldList.add(field.getName());
        }
        for(String c : column){
            if(!fieldList.contains(c)){
                resList.add(" ");
                continue;
            }
            PropertyDescriptor descriptor = new PropertyDescriptor(c,clazz);
            Method method = descriptor.getReadMethod();
            Object value = method.invoke(obj);
            resList.add(value==null?"":value);
        }
        return resList;
    }

    /**------------------------------------------------导入--------------------------------------------------------**/

    //循环行数
    private int rowNum = 0;
    //总行数
    private int totalRowNum = 0;
    //执行行数
    private int execRow = 10000;

    private int titleRow = 1;

    private Workbook wookbook;

    public Workbook getExcelWorkbook(MultipartFile file)throws Exception {
        //判断是否为excel类型文件
        String exclType = file.getOriginalFilename().endsWith(".xls") ? "xls" : file.getOriginalFilename().endsWith(".xlsx") ? "xlsx" : "none";
        if ("none".equals(exclType)) {
            throw new Exception("excel格式不正确！");
        }

        InputStream fis = file.getInputStream();
        Workbook wookbook = null;

        //获取一个绝对地址的流
//        fis = new FileInputStream(new File(filePath));
        if ("xls".equals(exclType)) {
            //2003版本的excel，用.xls结尾
            wookbook = new HSSFWorkbook(fis);//得到工作簿
        } else {
            wookbook = new XSSFWorkbook(fis);//得到工作簿
        }
        return wookbook;
    }

    /**
     * 读取出filePath中的所有数据信息
     * @param file excel文件
     * @param clazz 转换实体类
     *
     */
    public List<Object> getDataFromExcel(MultipartFile file, Class clazz)throws Exception
    {
        if(this.wookbook == null){
            wookbook = getExcelWorkbook(file);
        }
        //得到一个工作表
        Sheet sheet = wookbook.getSheetAt(0);
        //获得表头
        Row rowHead = sheet.getRow(titleRow);
        Map<Integer,String> headMap = new HashMap();
        for(int i = 1;i<rowHead.getPhysicalNumberOfCells();i++){
            System.out.println("=============="+rowHead.getCell(i));
            headMap.putAll(getRowMap(rowHead.getCell(i).getStringCellValue().trim(),i));
        }
        //获得数据的总行数
        totalRowNum = sheet.getLastRowNum();
        List<Object> voList = new ArrayList();
        rowNum += titleRow;
        execRow = rowNum + execRow;
        //获得所有数据
        for(;rowNum <= totalRowNum; )
        {
            rowNum ++;
            //获得第i行对象
            Row row = sheet.getRow(rowNum);
            Object o = clazz.newInstance();
            //初始化数据
            Field[] fields = clazz.getDeclaredFields();
            for(Field f : fields){
                if(f.getType().toString().endsWith("String")){
                    PropertyDescriptor descriptor = new PropertyDescriptor(f.getName(), o.getClass());
                    Method method = descriptor.getWriteMethod();
                    method.invoke(o,"");
                }
            }
            for(int j = 0;j < row.getPhysicalNumberOfCells(); j++){
                Cell cell = row.getCell(j);
                if(null == cell){
                    continue;
                }
                Object val = getCellValue(cell);
                String field = headMap.get(j);
                if(null != field){
                    getRefValue(o,field,val);
                }
            }
            voList.add(o);
            if(rowNum == execRow || rowNum == totalRowNum){
                return voList;
            }
        }
        return voList;
    }


    public Object getRefValue(Object o,String key,Object value) throws Exception {
        PropertyDescriptor descriptor = new PropertyDescriptor(key, o.getClass());
        Method method = descriptor.getWriteMethod();
        method.invoke(o,value);
        return o;
    }

    /**
     * 描述：对表格中数值进行格式化
     * @param cell
     * @return
     */
    public Object getCellValue(Cell cell){
        Object value = null;
        DecimalFormat df = new DecimalFormat("0");  //格式化number String字符
        SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd");  //日期格式化
        SimpleDateFormat sdf1 = new SimpleDateFormat("HH:mm:ss");  //日期格式化
        DecimalFormat df2 = new DecimalFormat("hh:mm:ss");  //格式化数字

        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_STRING:
                value = cell.getRichStringCellValue().getString();
                break;
            case Cell.CELL_TYPE_NUMERIC:
                if("General".equals(cell.getCellStyle().getDataFormatString())){
                    value = df.format(cell.getNumericCellValue());
                }else if("m/d/yy".equals(cell.getCellStyle().getDataFormatString())){
                    value = sdf.format(cell.getDateCellValue());
                }else if("hh:mm:ss".equals(cell.getCellStyle().getDataFormatString())){
                    value = sdf1.format(cell.getDateCellValue());
                }else{
                    value = df2.format(cell.getNumericCellValue());
                }
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                value = cell.getBooleanCellValue();
                break;
            case Cell.CELL_TYPE_BLANK:
                value = "";
                break;
            default:
                break;
        }
        return value;
    }

    //{"姓名","name"}返回｛1，"name"｝
    public Map<Integer,String> getRowMap(String name,int num){
        Map<Integer,String> resutlMap = new HashMap();
        Map<String,String> oMap = objMap;
        String value = oMap.get(name);
        resutlMap.put(num,value);
        return resutlMap;
    }

    public int getRowNum() {
        return rowNum;
    }

    public int getTotalRowNum() {
        return totalRowNum;
    }

    public int getExecRow() {
        return execRow;
    }

    public void setExecRow(int execRow) {
        this.execRow = execRow;
    }

    //通过前台传入的抬头名称转换字段数据返回
    public String[] getTitleField(String[] titleB){
        String[] result = new String[titleB.length];
        for(int i = 0;i<titleB.length;i++){
            if(null != objMap.get(titleB[i])){
                result[i] = objMap.get(titleB[i]);
            }
        }
        return result;
    }

}
